package cn.easyutil.util.javaUtil;

import java.io.*;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletResponse;

import cn.easyutil.util.javaUtil.annotation.ExcelValParse;
import cn.easyutil.util.javaUtil.annotation.ExcelValSwitch;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.easyutil.util.javaUtil.annotation.ExcelCellName;
import cn.easyutil.util.javaUtil.annotation.ExcelCallBack;

public class ExcelUtil {
	private Long test;
	private int rowMax;
	private int rowMin;
	private int cellMax;
	private int cellMin;
	private Workbook workbook = new HSSFWorkbook();
	public List<ExcelBean> getExcelBeans() {
		return excelBeans;
	}

	private List<ExcelBean> excelBeans = new ArrayList<ExcelUtil.ExcelBean>();

	public ExcelUtil(InputStream in, boolean isNewType) {
		this.readExcel(in, isNewType);
	}

	public ExcelUtil(InputStream in) {
		this.readExcel(in, false);
	}
	
	public ExcelUtil(){}
	
	public void toExcel(OutputStream out){
		try {
			this.workbook.write(out);
			this.workbook.close();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}
	
	public ExcelUtil createSheet(List<String> headers,List<List<String>> data,String sheetName){
		// 定义一个表
		Sheet sheet = this.workbook.createSheet(sheetName);
		// 获取Excel的格式 循环添加列名
		Row createRow = sheet.createRow(0);
		writeRow(headers, createRow);
		for (int i = 0; i < data.size(); i++) {
			Row row = sheet.createRow(i + 1);
			writeRow(data.get(i),row);
		}
		return this;
	}
	
	public <T> ExcelUtil createSheet(List<T> list,String sheetName){
		List<String> headers = getCellName(list.get(0));
		List<List<String>> data = new ArrayList<List<String>>();
		for (T t : list) {
			data.add(getCellValue(t));
		}
		return createSheet(headers, data, sheetName);
	}
	
	public ExcelUtil loadExcel(InputStream in, boolean isNewType){
		return this.readExcel(in, false);
	}

	/**
	 * 生成一个Excel的输出流
	 * 
	 * @param list
	 *            数据集合
	 * @param fileName
	 *            文件名
	 * @param response
	 */
	public static <T> void createExcel(List<T> list, String fileName, HttpServletResponse response) {
		if (StringUtil.isEmpty(response) || StringUtil.isEmpty(fileName)) {
			throw new NullPointerException("the parameter file name or HttpServletResponse must not be null");
		}
		response.reset();
		response.setContentType("application/msexcel");
		// 设置中文文件不乱码
		try {
			response.setHeader("Content-disposition","attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8") + ".xls");
		} catch (UnsupportedEncodingException e) {
			response.setHeader("Content-disposition","attachment;filename=" + fileName + ".xls");
		}
		List<String> headers = getCellName(list.get(0));
		List<List<String>> data = new ArrayList<List<String>>();
		for (T t : list) {
			data.add(getCellValue(t));
		}
		try {
			createExcel(headers, data, fileName, response.getOutputStream());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 生成excel
	 * @param headers
	 * @param data
	 * @param out
	 */
	public static void createExcel(List<String> headers,List<List<String>> data,String sheetName,OutputStream out){
		// 定义一个新的工作簿
		Workbook wb = new HSSFWorkbook();
		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		int start = 0;
		while (true){
			if(start >= data.size()){
				break;
			}
			List<List<String>> subData = null;
			if(data.size()-start > 60000){
				subData = data.subList(start, start+60000);
				start = start + 60000;
			}else{
				subData = data.subList(start, data.size());
				start = data.size();
			}
			// 定义一个表
			Sheet sheet = wb.createSheet(sheetName+start);
			// 获取Excel的格式 循环添加列名
			Row createRow = sheet.createRow(0);
			writeRow(headers, createRow);
			for (int i = 0; i < subData.size(); i++) {
				Row row = sheet.createRow(i + 1);
				writeRow(subData.get(i),row);
			}
		}
//		// 定义一个表
//		Sheet sheet = wb.createSheet(sheetName);
//		// 获取Excel的格式 循环添加列名
//		Row createRow = sheet.createRow(0);
//		writeRow(headers, createRow);
//		for (int i = 0; i < data.size(); i++) {
//			Row row = sheet.createRow(i + 1);
//			writeRow(data.get(i),row);
//		}
		// 放入输出流
		try {
			wb.write(out);
			wb.close();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
	}
	
	/**
	 * 生成excel
	 * @param list
	 * @param sheetName	
	 * @param out
	 */
	public static <T> void createExcel(List<T> list,String sheetName,OutputStream out){
		List<String> headers = getCellName(list.get(0));
		List<List<String>> data = new ArrayList<List<String>>();
		for (T t : list) {
			data.add(getCellValue(t));
		}
		createExcel(headers, data, sheetName, out);
	}
	
	/**
	 * 按header顺序生成excel
	 * @param headers
	 * @param data
	 * @param fileName
	 * @param out
	 */
	public static void createExcel(LinkedHashMap<String, Object> headers,List<Map<String,Object>> data,String fileName,OutputStream out){
		List<String> hs = new ArrayList<String>();
		List<String> hvs = new ArrayList<String>();
		List<List<String>> ds = new ArrayList<List<String>>();
		Set<Entry<String, Object>> set = headers.entrySet();
		Iterator<Entry<String, Object>> ite = set.iterator();
		while(ite.hasNext()){
			Entry<String, Object> entry = ite.next();
			hvs.add(entry.getKey());
			hs.add(entry.getValue().toString());
		}
		for (Map<String,Object> map : data) {
			List<String> oneData = new ArrayList<String>();
			for (String v : hvs) {
				if(map.containsKey(v)){
					oneData.add(map.get(v).toString());
				}else{
					oneData.add("");
				}
			}
			ds.add(oneData);
		}
		createExcel(hs, ds, fileName, out);
	}
	
	private static <T> void writeRow(List<T> data,Row row,ExcelCallBack...callBack){
		if(callBack!=null && callBack.length>0){
			callBack[0].write(data, row);
			return;
		}
		createHeader(row, data);
	}
	
	/** 创建表头 
	 * @param <T>*/
	private static <T> void createHeader(Row createRow, List<T> list) {
		if(StringUtil.isEmpty(list)){
			return;
		}
		for (int i = 0; i < list.size(); i++) {
			Cell cell = createRow.createCell(i);
			cell.setCellValue(list.get(i).toString());
		}
	}

	/**
	 * 获取表格中的表头信息
	 * 
	 * @param obj
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private static <T> List<String> getCellName(T obj) {
		List<String> result = new ArrayList<String>();
		for (Class objClass = obj.getClass(); !objClass.equals(Object.class); objClass = objClass.getSuperclass()) {
			Field[] fields = objClass.getDeclaredFields();
			for (Field f : fields) {
				Annotation[] ans = f.getDeclaredAnnotations();
				for (Annotation a : ans) {
					if (a instanceof ExcelCellName) {
						ExcelCellName name = (ExcelCellName) a;
						if (name.value() != null) {
							if (name.value().equals("")) {
								result.add("unkown");
							} else {
								result.add(name.value());
							}
						}
					}
				}
			}
		}
		return result;
	}

	/**
	 * 获取表格中内容
	 * 
	 * @param obj
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	private static <T> List<String> getCellValue(T obj) {
		List<String> result = new ArrayList<String>();
		for (Class objClass = obj.getClass(); !objClass.equals(Object.class); objClass = objClass.getSuperclass()) {
			Field[] fields = objClass.getDeclaredFields();
			for (Field f : fields) {
				ExcelCellName a = f.getDeclaredAnnotation(ExcelCellName.class);
				if(a == null){
					continue;
				}
				String value = "";
				try {
					//拿到字段值
					Object ant = ObjectUtil.getAttributeValue(obj, f.getName());
					value = ant.toString();
					//先判断是否是需要时间转换的
					if(!StringUtil.isEmpty(a.dateFormart())){
						String dateFormart = a.dateFormart();
						SimpleDateFormat format = new SimpleDateFormat(dateFormart);
						if(ant instanceof Date){
							Date date = (Date) ant;
							value = format.format(date);
						}else if(ant instanceof Long){
							Long date = (Long) ant;
							value = format.format(date);
						}else if(ant instanceof java.sql.Date){
							java.sql.Date date = (java.sql.Date) ant;
							value = format.format(date);
						}
						result.add(value);
						continue;
					}
					ExcelValParse parse = a.Parse();
					ExcelValSwitch aSwitch = a.Switch();
					//再处理自定义类和方法的
					if(parse.parseValueClass()!=Object.class && !StringUtil.isEmpty(parse.parseValueMethod())){
						Class<?> enumClass = parse.parseValueClass();
						String enumMothed = parse.parseValueMethod();
						Method method = null;
						//尝试两次，一次原值调用，一次基本数据类型转换后调用
						try {
							method = enumClass.getDeclaredMethod(enumMothed, f.getType());
						}catch (Exception e){
							method = enumClass.getDeclaredMethod(enumMothed, getReversBaseClass(f.getType()));
						}
						Object o = enumClass.newInstance();
						method.setAccessible(true);
						value = method.invoke(o, ant).toString();
						method.setAccessible(false);
						result.add(value);
						continue;
					}
					//再处理自定义switch
					else if(aSwitch.switchValues().length>0){
						String split = aSwitch.switchSplitBy();
						if(StringUtil.isEmpty(split)){
							split = "=";
						}
						Map<String,String> values = new HashMap<>();
						String[] vs = aSwitch.switchValues();
						for (String v : vs) {
							if(StringUtil.isEmpty(v)){
								continue;
							}
							String[] sp = v.split(split);
							if(sp==null || sp.length<1){
								continue;
							}
							values.put(sp[0],sp[1]);
						}
						if(!values.isEmpty()){
							String v = values.get(value);
							if(!StringUtil.isEmpty(v)){
								result.add(v);
								continue;
							}
						}
					}
					//其他情况全部使用自身的值
					result.add(value);
				}catch (Exception e){
					result.add(value);
				}

//				result.add(value);
//				for (Annotation a : ans) {
//					if (a instanceof ExcelCellName) {
//						try {
//							//拿到字段值
//							Object ant = ObjectUtil.getAttributeValue(obj, f.getName());
//							String value = ant.toString();
//							//判断是否需要转换时间戳字段
//							ExcelCellName excelCellName = (ExcelCellName) a;
//							if(!StringUtil.isEmpty(excelCellName.dateFormart())){
//								String dateFormart = excelCellName.dateFormart();
//								SimpleDateFormat format = new SimpleDateFormat(dateFormart);
//								if(ant instanceof Date){
//									Date date = (Date) ant;
//									value = format.format(date);
//								}else if(ant instanceof Long){
//									Long date = (Long) ant;
//									value = format.format(date);
//								}else if(ant instanceof java.sql.Date){
//									java.sql.Date date = (java.sql.Date) ant;
//									value = format.format(date);
//								}
//							}
//							//判断是否是枚举类
//							if(excelCellName.parseValueClass()!=Object.class && !StringUtil.isEmpty(excelCellName.parseValueMethod())){
//								Class<?> enumClass = excelCellName.parseValueClass();
//								String enumMothed = excelCellName.parseValueMethod();
//								Method method = enumClass.getDeclaredMethod(enumMothed, f.getType());
//								method.setAccessible(true);
//								value = method.invoke(enumClass, ant).toString();
//							}
//							result.add(value);
//						} catch (Exception e) {
//							result.add("");
//						}
//					}
//				}
			}
		}
		return result;
	}

	/**
	 * 基本数据类型取包装或反包装类型
	 * @param value
	 * @return
	 */
	private static Class getReversBaseClass(Class value){
		if(value == null){
			return null;
		}
		if(value.equals(Long.class)) {
			return long.class;
		} else if(value.equals(long.class)){
			return Long.class;
		}

		else if(value.equals(Integer.class)) {
			return int.class;
		} else if(value.equals(int.class)){
			return Integer.class;
		}

		else if(value.equals(Double.class)) {
			return double.class;
		}else if(value.equals(double.class)){
			return Double.class;
		}

		else if(value.equals(Float.class)) {
			return float.class;
		}else if(value.equals(float.class)){
			return Float.class;
		}

		else if(value.equals(Byte.class)) {
			return byte.class;
		}else if(value.equals(byte.class)){
			return Byte.class;
		}

		else if(value.equals(Boolean.class)) {
			return boolean.class;
		}else if(value.equals(boolean.class)){
			return Boolean.class;
		}

		else if(value.equals(Short.class)) {
			return short.class;
		}else if(value.equals(short.class)){
			return Short.class;
		}

		else if(value.equals(Character.class)) {
			return char.class;
		}else if(value.equals(char.class)){
			return Character.class;
		}

		return value;
	}

	/**
	 * 读取excel数据
	 * 
	 * @param in
	 * @param isNewType
	 * @return
	 */
	public ExcelUtil readExcel(InputStream in, boolean isNewType) {
		Workbook wb = null;
		if (in == null) {
			return null;
		}
		try {
			if (!isNewType) {
				wb = new HSSFWorkbook(in);
			} else {
				wb = new XSSFWorkbook(in);
			}
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		// 获取第一页
		Sheet sheet = wb.getSheetAt(0);
		// 获取最大行数
		int rowSize = sheet.getPhysicalNumberOfRows();
		// 循环行
		for (int i = 0; i < rowSize; i++) {
			Row row = sheet.getRow(i);
			// 获取最大列数
			int cellSize = row.getPhysicalNumberOfCells();
			// 循环此行的列
			for (int j = 0; j < cellSize; j++) {
				ExcelBean bean = new ExcelBean();
				// 获取单元格
				Cell cell = row.getCell(j);
				String obj = getCellData(cell);
				if (StringUtil.isEmpty(obj)) {
					continue;
				}
				bean.setRowNo(i);
				bean.setCellNo(j);
				bean.setData(obj);
				this.excelBeans.add(bean);
			}
		}
		try {
			wb.close();
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		for (ExcelBean ex : this.excelBeans) {
			if(ex.getRowNo()>rowMax){
				this.rowMax = ex.getRowNo();
			}
			if(ex.getRowNo()<rowMin){
				this.rowMin = ex.getRowNo();
			}
			if(ex.getCellNo()>cellMax){
				this.cellMax = ex.getCellNo();
			}
			if(ex.getCellNo()<cellMin){
				this.cellMin = ex.getCellNo();
			}
		}
		return this;
	}

	/**
	 * 获取单元格数据
	 * 
	 * @param cell
	 * @return
	 */
	private String getCellData(Cell cell) {
		if(cell == null){
			return null;
		}
		cell.setCellType(CellType.STRING);
		return cell.getStringCellValue();
	}

	public <T>List<T> toList(Class<T> clazz){
//		Map<String, ExcelCellName> map = ObjectUtil.getFieldAnnotation(clazz, ExcelCellName.class);
//		if (StringUtil.isEmpty(map)) {
//			return null;
//		}
//		ExcelBean bean = new ExcelBean();
//		for(String s : map.keySet()){
//			ExcelCellName an = map.get(s);
//			if(!an.value().equals("")){
//				bean = get(an.value());
//				break;
//			}
//		}
		return toList(clazz,0,this.rowMax);
	}
	/**
	 * 将读取到的excel转换成集合对象,仅支持固定格式(一行表头,多行内容)
	 * @param clazz	需要转换的对象类型
	 * @param startRow	开始行数
	 * @param endRow	结束行数
	 * @return
	 */
	public <T>List<T> toList(Class<T> clazz,int startRow,int endRow){
		if (StringUtil.isEmpty(this.excelBeans)) {
			return null;
		}
		// 获取类中含有EasyExcelCell注解的全部属性与注解
		Map<String, ExcelCellName> map = ObjectUtil.getFieldAnnotation(clazz, ExcelCellName.class);
		if (StringUtil.isEmpty(map)) {
			return null;
		}
		//准备返回数据
		List<T> result = new ArrayList<T>();
		//带有注解的字段名
		String[] fields = new String[map.size()];
		//带有注解的注解值
		ExcelBean[] anValues = new ExcelBean[map.size()];
		int i = 0;
		for(String s:map.keySet()){
			ExcelCellName an = map.get(s);
			//获取注解上的说明
			String value = an.value();
			fields[i] = s;
			//根据说明获取该说明在单元格上的位置
			anValues[i] = get(value);
			i++;
		}
		for (int j = startRow; j < endRow; j++) {
			T t = null;
			try {
				t = clazz.newInstance();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
			for (int j2 = 0; j2 < anValues.length; j2++) {
				Integer cellNo = anValues[j2].getCellNo();
				if(cellNo == null){
					continue;
				}
//				ExcelBean b = get(j, cellNo);
				ExcelBean b = match(j+1, cellNo, 2);
				if(b==null){
					continue;
				}
				try {
//					Field f = clazz.getDeclaredField(fields[j2]);
//					f.setAccessible(true);
//					Class<?> type = f.getType();
//					if(type.equals(Integer.class)){
//						f.set(t, new BigDecimal(b.getData()).intValue());
//					}else if(type.equals(Long.class)){
//						f.set(t, new BigDecimal(b.getData()).longValue());
//					}else if(type.equals(Double.class)){
//						f.set(t, new BigDecimal(b.getData()).doubleValue());
//					}else if(type.equals(Boolean.class)){
//						f.set(t, Boolean.valueOf(b.getData()));
//					}else{
//						f.set(t, b.getData());
//					}
//					f.setAccessible(false);
					ObjectUtil.setAttribute(t, fields[j2], b.getData());
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			result.add(t);
		}
		return result;
	}
	/**
	 * excel转bean
	 * @param clazz
	 * @return
	 */
	public <T> T toBean(Class<T> clazz) {
		if (StringUtil.isEmpty(this.excelBeans)) {
			return null;
		}
		// 获取类中含有EasyExcelCell注解的全部属性与注解
		Map<String, ExcelCellName> map = ObjectUtil.getFieldAnnotation(clazz, ExcelCellName.class);
		if (StringUtil.isEmpty(map)) {
			return null;
		}
		T t = null;
		try {
			t = clazz.newInstance();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		for (String s : map.keySet()) {
			ExcelCellName an = map.get(s);
			String value = an.value();
			if (value.equals("")) {
				continue;
			}
			ExcelBean bean = get(value);
			if(StringUtil.isEmpty(bean)){
				continue;
			}
			Integer rowNo = bean.getRowNo();
			Integer cellNo = bean.getCellNo();
			int location = an.location();
			ExcelBean match = match(rowNo, cellNo, location);
			if(StringUtil.isEmpty(match)){
				continue;
			}
			try {
				Field field = clazz.getDeclaredField(s);
				field.setAccessible(true);
				Class<?> type = field.getType();
				if(type.equals(Integer.class)){
					field.set(t, new BigDecimal(match.getData()).intValue());
				}else if(type.equals(Long.class)){
					field.set(t, new BigDecimal(match.getData()).longValue());
				}else if(type.equals(String.class)){
					field.set(t, match.getData());
				}else if(type.equals(Double.class)){
					field.set(t, new BigDecimal(match.getData()).doubleValue());
				}else if(type.equals(Boolean.class)){
					field.set(t, Boolean.valueOf(match.getData()));
				}else{
					field.set(t, match.getData());
				}
				field.setAccessible(false);
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
		return t;
	}

	private ExcelBean match(int row,int cell,int location){
		ExcelBean bean;
		switch (location) {
		case 1: {
			for (int i = 0; (row-i) >= this.cellMin; i++) {
				bean = get(row-i, cell);
				if(!StringUtil.isEmpty(bean)){
					return bean;
				}
			}
			return null;
		}
		case 2: {
			for (int i = 0; (row+i) <= this.rowMax; i++) {
				bean = get(row+i, cell);
				if(!StringUtil.isEmpty(bean)){
					return bean;
				}
			}
			return null;
		}
		case 3: {
			for (int i = 0; (cell-i) >= this.cellMin; i++) {
				bean = get(row, cell-i);
				if(!StringUtil.isEmpty(bean)){
					return bean;
				}
			}
			return null;
		}
		case 4: {
			for (int i = 0; (cell+i) <= this.cellMax; i++) {
				bean = get(row, cell+i);
				if(!StringUtil.isEmpty(bean)){
					return bean;
				}
			}
		}
		default:{
			return null;
		}
	}
	}
	public <T> List<T> excelToList(Class<T> clazz) {
		return null;
	}

	/**
	 * 获取第一条表中单元格数据
	 * 
	 * @param data
	 *            单元格内容
	 * @return
	 */
	public ExcelBean get(String data) {
		if (StringUtil.isEmpty(this.excelBeans)) {
			return null;
		}
		for (ExcelBean excelBean : this.excelBeans) {
			if (excelBean.getData().equals(data)) {
				return excelBean;
			}
		}
		return null;
	}

	/**
	 * 获取表中单元格数据
	 * 
	 * @param row
	 *            行号
	 * @param cell
	 *            列号
	 * @return
	 */
	public ExcelBean get(int row, int cell) {
		if (StringUtil.isEmpty(this.excelBeans)) {
			return null;
		}
		for (ExcelBean excelBean : this.excelBeans) {
			if (excelBean.getRowNo().equals(row) && excelBean.getCellNo().equals(cell)) {
				return excelBean;
			}
		}
		return null;
	}

	/**
	 * excel包装bean
	 * 
	 * @author spc
	 *
	 */
	public static class ExcelBean {
		// 行数
		private Integer rowNo;
		// 列数
		private Integer cellNo;
		private String data;

		public Integer getRowNo() {
			return rowNo;
		}

		public void setRowNo(Integer rowNo) {
			this.rowNo = rowNo;
		}

		public Integer getCellNo() {
			return cellNo;
		}

		public void setCellNo(Integer cellNo) {
			this.cellNo = cellNo;
		}

		public String getData() {
			return data;
		}

		public void setData(String data) {
			this.data = data;
		}

	}
}
